package LogicSide;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DBside.JDBCConnection;
import DBside.TableItem;
import DBside.Tools;
import DBside.JDBCConnection.Table;

public class ArtistModifier {
	/*
	 * public static void main(String[] args) throws SQLException {
	 * JDBCConnection con = new JDBCConnection();
	 * con.openConnection(Tools.LocalURL, Tools.LocaluserName,
	 * Tools.Localpassword); Session s=new Session(); s.setCon(con);
	 * //System.out.println(addNewArtist("oaaa333a555", s));
	 * //System.out.println(removeArtist("o b 1", s));
	 * //System.out.println(removeArtistAndSongs("A, C&T", s)); }
	 */
	// enter album_name to db with new id if not there already, otherwise return
	// the id from the db
	/**
	 * 
	 * @param artist_name
	 * @param s
	 * @return add new artist to db, return id if already in db or if added, -1
	 *         for errors
	 * @throws SQLException
	 */
	public static int addNewArtist(String artist_name, Session s)
			throws SQLException {
		int artist_id = -1;
		ResultSet artist_rs = null;
		TableItem[] ti = new TableItem[1];
		try {
			s.getCon().getConnection().setAutoCommit(false);
			ti[0] = new TableItem("name", Tools.Type.VARCHAR, artist_name);
			artist_rs = s.getCon().select("id", ti, null, null,
					JDBCConnection.Table.ARTIST);
			if (Tools.isEmpty(artist_rs)) {// not in db. get new id and add
											// TODO thats not good enough
				artist_rs = s.getCon().insert(JDBCConnection.Table.ARTIST, ti);
				if (Tools.isEmpty(artist_rs)) {
					// TODO problem
				} else {
					artist_rs.next();
					s.getCon().getConnection().commit();

					return artist_rs.getInt(1);
				}
			} else {// in db. return it's id.
				artist_rs.next();
				s.getCon().getConnection().commit();

				return artist_rs.getInt("id");
			}
			s.getCon().getConnection().commit();
		} catch (SQLException e) {
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		} finally {
			if (artist_rs != null)
				artist_rs.close();
		}
		return artist_id;
	}

	/**
	 * 
	 * @param artist_name
	 * @param s
	 * @return deletes artist and returns its id, return -1 for errors
	 * @throws SQLException
	 */
	public static int removeArtist(String artist_name, Session s)
			throws SQLException {// if in db remove, return id of removed artist
									// or -1 if wasnt in db
		TableItem[] ti = new TableItem[1];
		int artist_id = -1;
		ResultSet artist_rs = null;
		try {
			s.getCon().getConnection().setAutoCommit(false);

			ti[0] = new TableItem("name", Tools.Type.VARCHAR, artist_name);
			artist_rs = s.getCon().select("id", ti, null, null,
					JDBCConnection.Table.ARTIST);
			if (!Tools.isEmpty(artist_rs)) {
				artist_rs.next();
				artist_id = artist_rs.getInt("id");
				s.getCon().delete(ti, null, Table.ARTIST);
			}
			s.getCon().getConnection().commit();

		} catch (SQLException e) {
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		} finally {
			if (artist_rs != null)
				artist_rs.close();
		}
		return artist_id;
	}
	/**
	 * 
	 * @param artist_name
	 * @param s
	 * @return remove artist and all related songs, returns ids of all deleted items, empty list for errors
	 * @throws SQLException
	 */
	public static List<Integer> removeArtistAndSongs(String artist_name,
			Session s) throws SQLException {// returned list has all ids of
											// removed songs and then id of
											// removed artist
		int artist_id = removeArtist(artist_name, s);
		ResultSet song_rs = null;
		try {
			s.getCon().getConnection().setAutoCommit(false);

			if (artist_id != -1) {
				// now get all songs id's and delete them using the deleteSong
				// procedure
				TableItem[] ti = new TableItem[1];
				ti[0] = new TableItem("artist_id", Tools.Type.INT, artist_id);
				song_rs = s.getCon().select("song_id", ti, null, null,
						JDBCConnection.Table.ARTISTVSSONG);
				List<Integer> removedArtistAndSongs = new ArrayList<Integer>();
				if (!Tools.isEmpty(song_rs)) {// if empty nothing to do, so take
												// all song_id and delete them
					while (song_rs.next()) {
						removedArtistAndSongs.add(song_rs.getInt("song_id"));
					}
				}
				// s.getCon().delete(ti, null, Table.ARTISTVSSONG);//removes all
				// songs with that artist from artistvssong TODO delete this
				// when elad fixes deleteSong

				for (Integer s_id : removedArtistAndSongs)
					ManageSongs.deleteSong(s_id, s);

				removedArtistAndSongs.add(artist_id);
				s.getCon().getConnection().commit();
				return removedArtistAndSongs;

			}
			s.getCon().getConnection().commit();

		} catch (SQLException e) {
			if (s.getCon() != null)
				s.getCon().getConnection().rollback();
		} finally {
			if (song_rs != null)
				song_rs.close();
		}
		return null;
	}
}
